﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Windows.Forms;
using Oracle.DataAccess;        // Muito importante para acesso ao DB 
using Oracle.DataAccess.Client; // Muito importante para acesso ao BD
using Oracle.DataAccess.Types;
namespace ProjMin.Database {
    class DbConnection {

        private XmlNode config = null;
        private OracleConnection oraCon = null;
        public DbConnection(XmlNode config) {
            this.config = config;

            //this.generateTNS();

            this.initDbConnection();
        }

        private String generateTNS() {
            string oradb = null;
            try {

                oradb = "Data Source=(DESCRIPTION="
                 + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + this.config["database-host"].InnerText + ")(PORT=" + this.config["database-port"].InnerText + ")))"
                 + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=" + this.config["database-name"].InnerText + ")));"
                 + "User Id=" + this.config["database-user"].InnerText + ";Password=" + this.config["database-password"].InnerText + ";";
            }
            catch (Exception ex) {
                System.Console.WriteLine(ex.StackTrace);

            }
            return oradb;
        }

        private void initDbConnection() {
            this.oraCon = new OracleConnection(this.generateTNS());
            try {
                this.oraCon.Open();
            }
            catch (OracleException ex) {
                MessageBox.Show(ex.Message,
                "Erro de Conexão com Banco",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);

                // Este erro é critico. não Podemos continuar...
                // Implementar saída da aplicação aqui                

            }
        }

        /// <summary>
        /// Executa uma consulta no banco retornando co DataReader
        /// </summary>
        /// <param name="sql">Query a ser executada</param>
        /// <returns>OracleDataReader</returns>
        public OracleDataReader executeSql(String sql) {
            OracleDataReader dr = null;
            try {
                OracleCommand cmd = new OracleCommand();

                cmd.Connection = this.oraCon;
                cmd.CommandText = sql;

                dr = cmd.ExecuteReader();
            }
            catch (OracleException ex) {
                MessageBox.Show(ex.Message,
                "Erro ao executar a query",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);

            }
            return dr;
        }

        public String getKeyValue(String key_name) {
            String r = null;
            try {
                OracleDataReader dr = this.executeSql("SELECT * FROM CONFIGURATION WHERE UPPER(KEY_NAME) = UPPER('" + key_name + "')");
                dr.Read();

                r = dr["KEY_VALUE"].ToString();
            }
            catch (OracleException ex) {
                MessageBox.Show(ex.Message,
                "Erro ao obter chave",
                MessageBoxButtons.OK,
                MessageBoxIcon.Exclamation,
                MessageBoxDefaultButton.Button1);

            }
            catch (Exception ex) {
                MessageBox.Show(ex.Message,
                   "Erro ao obter chave",
                   MessageBoxButtons.OK,
                   MessageBoxIcon.Exclamation,
                   MessageBoxDefaultButton.Button1);
            }
            return r;
        }
    }
}
